#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database if not exists edu_dws;
use edu_dws;

drop table if exists wf_dws_cus_day_cnt;
create table wf_dws_cus_day_cnt(
    dt                       string,  --create_date_time
    origin_type              string comment '数据来源',
    origin_channel           string comment '来源渠道',
    clue_state               string comment '线索状态',
    area                     string comment '区域',
    center_name              string comment '部门名称',-- dep_name
    school_id                int,
    school_name              string comment '校区名称',
    subject_id               int,
    subject_name             string comment '学科名称',
    group_type               string comment 'no,origin,area,sub,school,center,all',
    rel_cnt               bigint comment '意向总个数'
)comment '意向主题日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

drop table if exists wf_dws_clu_hour_cnt;
create table wf_dws_clu_hour_cnt(
    dt  string,
    hr                       string comment '创建时间',-- create_date_time
    clue_state               string comment '线索状态',
    origin_type              string comment '数据来源',
    clu_day_cnt                  bigint comment '每天效线索总个数',
    clu_hour_cnt                  bigint comment '每小时有效线索总个数',
    eff_hour_cnt                     bigint comment '每小时有效线索总个数',
    clu_hour_rate               decimal(38,2) comment '每小时线索转换率'
)comment '线索主题小时统计表'
row format delimited
fields terminated by '\t';"